  Use master
  GO

  IF EXISTS (SELECT * FROM sysdatabases WHERE NAME ='SIC')
  BEGIN
  	ALTER DATABASE SIC SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  	Drop database SIC
  END
  GO
   
  Use master
  GO
  Create database SIC
  COLLATE SQL_Latin1_General_CP850_CS_AS -- or any collation you require
  GO

  use SIC
  GO
  /*INTEGRACION CON VISTA */
  CREATE VIEW Productos
  AS
  	SELECT Item_strItemId,Item_strItemDescription,Item_strItemShortDesc,Item_curRetailPrice,Item_strStatus
    FROM VISTA.dbo.tblItem
  GO

  create view Productos_Inventario
  AS
    SELECT Item_strItemId,Item_strItemDescription,Item_strItemShortDesc,Item_curRetailPrice,Item_strStatus
    FROM VISTA.dbo.tblItem
    WHERE Item_strReportCode1 like '%|INV|%'
  GO

  CREATE VIEW Usuarios
  AS
  	SELECT User_intUserNo,User_strFirstName,User_strLastName,User_strEnabled,User_strHOPayrollCode,User_intPIN,UserG_strCode,User_strSecurityId
     FROM VISTA.dbo.tblUser
  GO

  CREATE VIEW Sucursal
  AS
  	SELECT Cinema_strCode,Cinema_strBranchNo,Cinema_strName,Cinema_strShortName,Cinema_strNameLegal,Cinema_strAddress1
     FROM VISTA.dbo.tblCinema
  GO

  CREATE vIEW PuntosVentas
  AS
  	SELECT Workstation_strCode,Workstation_strName,Workstation_strEnabled
    FROM VISTA.dbo.tblWorkstation
  	WHERE Workstation_strName LIKE '%CO%'
  GO

  CREATE VIEW Session_POS
  AS

  	SELECT  POSS_intPOSSessionNumber,User_intUserNo,POSS_strUserName, POSS_dtmBusinessDate
    FROM VISTA.dbo.tblPOS_Sess
  GO

  CREATE VIEW Inventory
  AS
    SELECT * FROM VISTA.dbo.tblTrans_Inventory
  GO

  CREATE PROCEDURE SP_Productos_L
  as
  	SELECT * FROM Productos_Inventario
  	order by Item_strItemDescription
  GO

  CREATE PROCEDURE SP_Usuarios_L
  as
  	SELECT * FROM Usuarios
  	ORDER BY User_strFirstName
  GO

  CREATE PROCEDURE SP_Sucursal_L
  as
  	SELECT * FROM Sucursal
  GO

  CREATE PROCEDURE SP_PuntosVentas_L
  as
  	SELECT * FROM PuntosVentas
  	Order by Workstation_strName
  GO

  create table turnos (
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
      [fecha_inicio] [datetime] NOT NULL,
      [fecha_termino] [datetime]  NULL,
      [id_sucursal] [varchar] (10) NOT NULL,
      [cod_sucursal] [varchar] (10) NOT NULL,
      [id_punto_venta] [varchar] (20) NOT NULL,
      [id_cajero] [bigint] NOT NULL,
      [id_supervisor_apertura] [bigint] NOT NULL,
      [id_supervisor_cierre] [bigint]  NULL,
      [id_estado] [bigint] NOT NULL,
      [create_date] [datetime] default getDate()
  )
  GO

  create table turnos_estados (
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
     	[nombre] [varchar] (50) NOT NULL,
      [create_date] [datetime] default getDate()
  )
  GO

  create table reposiciones (
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
      [id_sucursal] [varchar] (10) NOT NULL,
      [cod_sucursal] [varchar] (10) NOT NULL,
      [id_turno] [bigint] NOT NULL,
      [id_supervisor] [bigint] NOT NULL,
      [fecha] [varchar] (255) NOT NULL,
      [es_inicio] bit not null,
      [create_date] [datetime] default getDate()
  )
  GO

  create table reposiciones_detalles (
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
      [id_sucursal] [varchar] (10) NOT NULL,
      [cod_sucursal] [varchar] (10) NOT NULL,
      [id_reposicion] [bigint] NOT NULL,
      [id_producto] [varchar] (30) NOT NULL,
      [cantidad] [bigint] NOT NULL,
      [es_inicio] bit NOT NULL,
      [create_date] [datetime] default getDate()
  )
  GO

  create table cierre_turnos(
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY, 
      [id_sucursal] [varchar] (10) NOT NULL,
      [cod_sucursal] [varchar] (10) NOT NULL,
      [id_turno] [bigint] NOT NULL,
   	  [id_supervisor] [bigint] NOT NULL,
      [id_punto_venta][varchar] (30) NOT NULL,
      [fecha] [datetime]  NOT NULL,
      [fecha_negocio] [datetime]  NOT NULL,
      [turno_cuadrado] bit not null,
      [diferencia_productos] bigint,
      [diferencia_pesos] bigint,
      [session_pos_id] bigint,
      [session_pos_fecha_negocio] [datetime]  NOT NULL,
      [observacion] varchar (800) not null,
      [create_date] [datetime] default getDate()
  )
  GO

  create table cierre_turnos_detalles (
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
      [id_sucursal] [varchar] (10) NOT NULL,
      [cod_sucursal] [varchar] (10) NOT NULL,
      [id_cierre] [bigint] NOT NULL,
      [id_producto] [varchar] (30) NOT NULL,
      [cantidad_real] [bigint]  NOT NULL,
      [cantidad_vendida] [bigint]  NOT NULL,
      [cantidad_entregada] [bigint]  NOT NULL,
      [precio_unitario] bigint not null,
      [create_date] [datetime] default getDate()
  )
  GO

  create table observaciones (
      [id] [bigint] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
      [id_sucursal] [varchar] (10) NOT NULL,
      [cod_sucursal] [varchar] (10) NOT NULL,
      [id_turno] [bigint] NOT NULL,
      [id_supervisor] [bigint] NOT NULL,
      [observacion] [varchar] (800) not null,
      [create_date] [datetime] default getDate()
  )
  GO
/*  LLAVES FORANEAS */ 
ALTER TABLE [dbo].[reposiciones]
  ADD CONSTRAINT [reposiciones_fk]
  FOREIGN KEY([id_turno])
  REFERENCES [dbo].[turnos]([id])
GO

ALTER TABLE [dbo].[cierre_turnos]
  ADD CONSTRAINT [cierre_turnos_fk]
  FOREIGN KEY([id_turno])
  REFERENCES [dbo].[turnos]([id])
GO

ALTER TABLE [dbo].[turnos]
  ADD CONSTRAINT [estados_fk]
  FOREIGN KEY([id_estado])
  REFERENCES [dbo].[turnos_estados]([id])
GO

ALTER TABLE [dbo].[observaciones]
  ADD CONSTRAINT [observaciones_fk]
  FOREIGN KEY([id_turno])
  REFERENCES [dbo].[turnos]([id])
GO

ALTER TABLE [dbo].[cierre_turnos_detalles]
  ADD CONSTRAINT [cierre_turnos_detalles_fk]
  FOREIGN KEY([id_cierre])
  REFERENCES [dbo].[cierre_turnos]([id])
GO

ALTER TABLE [dbo].[reposiciones_detalles]
  ADD CONSTRAINT [reposicion_detalles_fk]
  FOREIGN KEY([id_reposicion])
  REFERENCES [dbo].[reposiciones]([id])
GO


  /* PROCEDIMIENTOS ALMACENADOS */
  CREATE PROCEDURE sp_turnos_c
  			     @fecha_inicio datetime
             ,@fecha_termino datetime
             ,@id_sucursal varchar(10)
             ,@cod_sucursal varchar(10)
             ,@id_punto_venta varchar(20)
             ,@id_cajero bigint
             ,@id_supervisor_apertura bigint
             ,@id_supervisor_cierre bigint
             ,@id_estado bigint
             ,@id bigint OUTPUT
  as
  	INSERT INTO turnos
             ([fecha_inicio]
             ,[fecha_termino]
             ,[id_sucursal]
             ,[cod_sucursal]
             ,[id_punto_venta]
             ,[id_cajero]
             ,[id_supervisor_apertura]
             ,[id_supervisor_cierre]
             ,[id_estado])
       VALUES
             (@fecha_inicio
             ,@fecha_termino
             ,@id_sucursal
             ,@cod_sucursal
             ,@id_punto_venta
             ,@id_cajero
             ,@id_supervisor_apertura
             ,@id_supervisor_cierre
             ,@id_estado)
             
  			select @id = SCOPE_IDENTITY()
  			select @id
  GO

  create procedure sp_reposiciones_c
              @id_sucursal varchar(10)
             ,@cod_sucursal varchar(10)
             ,@id_turno bigint
             ,@id_supervisor bigint
             ,@fecha varchar(255)
             ,@es_inicio  bit
             ,@id bigint OUTPUT
  AS
    INSERT INTO [dbo].[reposiciones]
             ([id_sucursal]
             ,[cod_sucursal]
             ,[id_turno]
             ,[id_supervisor]
             ,[fecha]
             ,[es_inicio])
       VALUES
             (@id_sucursal
             ,@cod_sucursal
             ,@id_turno
             ,@id_supervisor
             ,@fecha
             ,@es_inicio)

        select @id = SCOPE_IDENTITY()
        select @id
  GO

  create procedure sp_cierre_turnos_c
                @id_sucursal varchar(10)
               ,@cod_sucursal varchar(10)
               ,@id_turno bigint
               ,@id_supervisor bigint
               ,@id_punto_venta varchar(30)
               ,@fecha datetime
               ,@turno_cuadrado bit
               ,@diferencia_productos bigint
               ,@diferencia_pesos bigint
               ,@observacion varchar(800)
               ,@fecha_negocio datetime
               ,@session_pos_id bigint
               ,@session_pos_fecha_negocio datetime
               ,@id bigint OUTPUT
  AS
        INSERT INTO [dbo].[cierre_turnos]
               ([id_sucursal]
               ,[cod_sucursal]
               ,[id_turno]
               ,[id_supervisor]
               ,[id_punto_venta]
               ,[fecha]
               ,[turno_cuadrado]
               ,[observacion]
               ,diferencia_productos
               ,diferencia_pesos
               ,fecha_negocio
               ,session_pos_id
               ,session_pos_fecha_negocio)
         VALUES
               (@id_sucursal
               ,@cod_sucursal
               ,@id_turno
               ,@id_supervisor
               ,@id_punto_venta
               ,@fecha
               ,@turno_cuadrado
               ,@observacion
               ,@diferencia_productos
               ,@diferencia_pesos
               ,@fecha_negocio
               ,@session_pos_id
               ,@session_pos_fecha_negocio)
        
        select @id = SCOPE_IDENTITY()
        select @id
  GO

  create procedure sp_cierre_turnos_detalles_c
              @id_sucursal varchar(10)
             ,@cod_sucursal varchar(10)
             ,@id_cierre bigint
             ,@id_producto varchar(30)
             ,@cantidad_real bigint
             ,@cantidad_vendida bigint
             ,@cantidad_entregada bigint
             ,@precio_unitario bigint
             ,@id bigint OUTPUT
  as
        INSERT INTO [dbo].[cierre_turnos_detalles]
             ([id_sucursal]
             ,[cod_sucursal]
             ,[id_cierre]
             ,[id_producto]
             ,[cantidad_real]
             ,[cantidad_vendida]
             ,[cantidad_entregada]
             ,[precio_unitario])
        VALUES
             (@id_sucursal
             ,@cod_sucursal
             ,@id_cierre
             ,@id_producto
             ,@cantidad_real
             ,@cantidad_vendida
             ,@cantidad_entregada
             ,@precio_unitario)

        select @id = SCOPE_IDENTITY()
        select @id
  GO




  create procedure sp_reposiciones_detalles_c
              @id_sucursal varchar(10)
             ,@cod_sucursal varchar(10)
             ,@id_reposicion  bigint
             ,@id_producto varchar(30)
             ,@cantidad bigint
             ,@es_inicio  bit
             ,@id bigint OUTPUT
  AS
      INSERT INTO [dbo].[reposiciones_detalles]
             ([id_sucursal]
             ,[cod_sucursal]
             ,[id_reposicion]
             ,[id_producto]
             ,[cantidad]
             ,[es_inicio])
       VALUES
             (@id_sucursal
             ,@cod_sucursal
             ,@id_reposicion
             ,@id_producto
             ,@cantidad
             ,@es_inicio)

        select @id = SCOPE_IDENTITY()
        select @id
  GO


  CREATE PROCEDURE sp_turnos_abiertos_l
  as
    select [id] as turno_id
          ,[fecha_inicio] as turno_fecha_inicio 
          ,[fecha_termino] as turno_fecha_termino
          ,[id_sucursal] as turno_id_sucursal
          ,[cod_sucursal] as turno_cod_sucursal
          ,[id_punto_venta] as turno_id_punto_venta
          ,[id_cajero] as turno_id_cajero
          ,[id_supervisor_apertura] as turno_id_supervisor_apertura
          ,[id_supervisor_cierre] as turno_id_supervisor_cierre
          ,[id_estado] as turno_id_estado
          ,sup.User_intUserNo  as supervisor_User_intUserNo
          ,sup.User_strFirstName as supervisor_User_strFirstName
          ,sup.User_strLastName as supervisor_User_strLastName
          ,sup.User_strEnabled as supervisor_User_strEnabled
          ,sup.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
          ,sup.User_intPIN as supervisor_User_intPIN
          ,sup.UserG_strCode as supervisor_UserG_strCode
        ,Usuarios.*
        ,PuntosVentas.*
    from turnos, Usuarios as Usuarios,PuntosVentas,Usuarios as sup
    where  turnos.id_estado=1
    and turnos.id_cajero=Usuarios.User_intUserNo
    and turnos.id_supervisor_apertura =  sup.User_intUserNo
    and PuntosVentas.Workstation_strCode = turnos.id_punto_venta
  GO

  CREATE PROCEDURE sp_turnos_cerrados_l
  as
    select turnos.[id] as turno_id
          ,turnos.[fecha_inicio] as turno_fecha_inicio 
          ,turnos.[fecha_termino] as turno_fecha_termino
          ,turnos.[id_sucursal] as turno_id_sucursal
          ,turnos.[cod_sucursal] as turno_cod_sucursal
          ,turnos.[id_punto_venta] as turno_id_punto_venta
          ,turnos.[id_cajero] as turno_id_cajero
          ,turnos.[id_supervisor_apertura] as turno_id_supervisor_apertura
          ,turnos.[id_supervisor_cierre] as turno_id_supervisor_cierre
          ,turnos.[id_estado] as turno_id_estado
          ,sup.User_intUserNo  as supervisor_User_intUserNo
          ,sup.User_strFirstName as supervisor_User_strFirstName
          ,sup.User_strLastName as supervisor_User_strLastName
          ,sup.User_strEnabled as supervisor_User_strEnabled
          ,sup.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
          ,sup.User_intPIN as supervisor_User_intPIN
          ,sup.UserG_strCode as supervisor_UserG_strCode
          ,cierre_turnos.id as cierre_turnos_id
          ,cierre_turnos.fecha as cierre_turnos_fecha
          ,cierre_turnos.turno_cuadrado as cierre_turnos_turno_cuadrado
          ,cierre_turnos.observacion as cierre_turnos_observacion
          ,cierre_turnos.diferencia_productos as cierre_turnos_diferencia_productos
          ,cierre_turnos.diferencia_pesos as cierre_turnos_diferencia_pesos
          ,cierre_turnos.fecha_negocio  as cierre_turnos_fecha_negocio
        ,Usuarios.*
        ,PuntosVentas.*
    from turnos, Usuarios as Usuarios,PuntosVentas,Usuarios as sup,cierre_turnos
    where  turnos.id_estado=2
    and turnos.id_cajero=Usuarios.User_intUserNo
    and turnos.id_supervisor_apertura =  sup.User_intUserNo
    and PuntosVentas.Workstation_strCode = turnos.id_punto_venta
    and turnos.id = cierre_turnos.id_turno
  GO

  CREATE PROCEDURE sp_turnos_cerrados_ultimos_l
  as
    select top 6 turnos.[id] as turno_id
          ,turnos.[fecha_inicio] as turno_fecha_inicio 
          ,turnos.[fecha_termino] as turno_fecha_termino
          ,turnos.[id_sucursal] as turno_id_sucursal
          ,turnos.[cod_sucursal] as turno_cod_sucursal
          ,turnos.[id_punto_venta] as turno_id_punto_venta
          ,turnos.[id_cajero] as turno_id_cajero
          ,turnos.[id_supervisor_apertura] as turno_id_supervisor_apertura
          ,turnos.[id_supervisor_cierre] as turno_id_supervisor_cierre
          ,turnos.[id_estado] as turno_id_estado
          ,sup.User_intUserNo  as supervisor_User_intUserNo
          ,sup.User_strFirstName as supervisor_User_strFirstName
          ,sup.User_strLastName as supervisor_User_strLastName
          ,sup.User_strEnabled as supervisor_User_strEnabled
          ,sup.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
          ,sup.User_intPIN as supervisor_User_intPIN
          ,sup.UserG_strCode as supervisor_UserG_strCode
          ,cierre_turnos.id as cierre_turnos_id
          ,cierre_turnos.fecha as cierre_turnos_fecha
          ,cierre_turnos.turno_cuadrado as cierre_turnos_turno_cuadrado
          ,cierre_turnos.observacion as cierre_turnos_observacion
          ,cierre_turnos.diferencia_productos as cierre_turnos_diferencia_productos
          ,cierre_turnos.diferencia_pesos as cierre_turnos_diferencia_pesos
          ,cierre_turnos.fecha_negocio  as cierre_turnos_fecha_negocio
        ,Usuarios.*
        ,PuntosVentas.*
    from turnos, Usuarios as Usuarios,PuntosVentas,Usuarios as sup,cierre_turnos
    where  turnos.id_estado=2
    and turnos.id_cajero=Usuarios.User_intUserNo
    and turnos.id_supervisor_apertura =  sup.User_intUserNo
    and PuntosVentas.Workstation_strCode = turnos.id_punto_venta
    and turnos.id = cierre_turnos.id_turno
    order by turnos.fecha_termino desc
  GO

  CREATE PROCEDURE sp_turnos_cerrados_por_fecha_l
              @fecha_inicio datetime
             ,@fecha_termino datetime
  as
    select turnos.[id] as turno_id
          ,turnos.[fecha_inicio] as turno_fecha_inicio 
          ,turnos.[fecha_termino] as turno_fecha_termino
          ,turnos.[id_sucursal] as turno_id_sucursal
          ,turnos.[cod_sucursal] as turno_cod_sucursal
          ,turnos.[id_punto_venta] as turno_id_punto_venta
          ,turnos.[id_cajero] as turno_id_cajero
          ,turnos.[id_supervisor_apertura] as turno_id_supervisor_apertura
          ,turnos.[id_supervisor_cierre] as turno_id_supervisor_cierre
          ,turnos.[id_estado] as turno_id_estado
          ,sup.User_intUserNo  as supervisor_User_intUserNo
          ,sup.User_strFirstName as supervisor_User_strFirstName
          ,sup.User_strLastName as supervisor_User_strLastName
          ,sup.User_strEnabled as supervisor_User_strEnabled
          ,sup.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
          ,sup.User_intPIN as supervisor_User_intPIN
          ,sup.UserG_strCode as supervisor_UserG_strCode
          ,cierre_turnos.id as cierre_turnos_id
          ,cierre_turnos.fecha as cierre_turnos_fecha
          ,cierre_turnos.turno_cuadrado as cierre_turnos_turno_cuadrado
          ,cierre_turnos.observacion as cierre_turnos_observacion
          ,cierre_turnos.diferencia_productos as cierre_turnos_diferencia_productos
          ,cierre_turnos.diferencia_pesos as cierre_turnos_diferencia_pesos
          ,cierre_turnos.fecha_negocio  as cierre_turnos_fecha_negocio
        ,Usuarios.*
        ,PuntosVentas.*
    from turnos, Usuarios as Usuarios,PuntosVentas,Usuarios as sup,cierre_turnos
    where  turnos.id_estado=2
    and turnos.id_cajero=Usuarios.User_intUserNo
    and turnos.id_supervisor_apertura =  sup.User_intUserNo
    and PuntosVentas.Workstation_strCode = turnos.id_punto_venta
    and turnos.id = cierre_turnos.id_turno
    and fecha_negocio between @fecha_inicio and @fecha_termino
  GO

  CREATE PROCEDURE sp_usuarios_cajeros_sin_turno_activo_l
  as
    SELECT * 
    FROM Usuarios 
    WHERE  UserG_strCode='EMP' and User_strEnabled='Y' and User_strHOPayrollCode <> '' 
    and User_intUserNo not in (select id_cajero from turnos where id_estado=1)
  GO


  CREATE PROCEDURE sp_turnos_por_id_l
        @id bigint
  as
    select [id] as turno_id
          ,[fecha_inicio] as turno_fecha_inicio 
          ,[fecha_termino] as turno_fecha_termino
          ,[id_sucursal] as turno_id_sucursal
          ,[cod_sucursal] as turno_cod_sucursal
          ,[id_punto_venta] as turno_id_punto_venta
          ,[id_cajero] as turno_id_cajero
          ,[id_supervisor_apertura] as turno_id_supervisor_apertura
          ,[id_supervisor_cierre] as turno_id_supervisor_cierre
          ,[id_estado] as turno_id_estado
          ,sup.User_intUserNo  as supervisor_User_intUserNo
          ,sup.User_strFirstName as supervisor_User_strFirstName
          ,sup.User_strLastName as supervisor_User_strLastName
          ,sup.User_strEnabled as supervisor_User_strEnabled
          ,sup.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
          ,sup.User_intPIN as supervisor_User_intPIN
          ,sup.UserG_strCode as supervisor_UserG_strCode
          ,Usuarios.*
          ,PuntosVentas.*
    FROM turnos, Usuarios,PuntosVentas,Usuarios as sup
    WHERE turnos.id_cajero=Usuarios.User_intUserNo
    and turnos.id_supervisor_apertura =  sup.User_intUserNo
    and PuntosVentas.Workstation_strCode = turnos.id_punto_venta
    and turnos.id=@id
  GO


  CREATE PROCEDURE sp_reposiciones_por_id_turno_l
        @id bigint
  as
    select Usuarios.User_intUserNo  as supervisor_User_intUserNo
          ,Usuarios.User_strFirstName as supervisor_User_strFirstName
          ,Usuarios.User_strLastName as supervisor_User_strLastName
          ,Usuarios.User_strEnabled as supervisor_User_strEnabled
          ,Usuarios.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
          ,Usuarios.User_intPIN as supervisor_User_intPIN
          ,Usuarios.UserG_strCode as supervisor_UserG_strCode
          ,reposiciones.id as reposicion_id
          ,reposiciones.id_sucursal as reposicion_id_sucursal
          ,reposiciones.cod_sucursal as reposicion_cod_sucursal
          ,reposiciones.id_supervisor as reposicion_id_supervisor
          ,reposiciones.id_turno as reposicion_id_turno
          ,reposiciones.fecha as reposicion_fecha
          ,reposiciones.es_inicio as reposicion_es_inicio
          ,reposiciones_detalles.id  as reposiciones_detalles_id
          ,reposiciones_detalles.id_producto as reposiciones_detalles_id_producto
          ,reposiciones_detalles.cantidad as reposiciones_detalles_cantidad
          ,reposiciones_detalles.es_inicio as reposiciones_detalles_es_inicio
          ,Productos.*
    from reposiciones,reposiciones_detalles,Usuarios,Productos  
    where reposiciones.id_turno=@id
    and reposiciones.id = reposiciones_detalles.id_reposicion
    and reposiciones.id_supervisor = Usuarios.User_intUserNo
    and reposiciones_detalles.id_producto = Productos.Item_strItemId
    order by reposiciones.id
  GO


  CREATE PROCEDURE sp_turnos_cierre_u
             @id bigint
             ,@fecha_termino datetime
             ,@id_supervisor_cierre bigint
  as
    update turnos 
      set  fecha_termino = @fecha_termino, id_supervisor_cierre = @id_supervisor_cierre,id_estado = 2
      where id=@id      
  GO


  create procedure sp_cierre_turnos_por_id_turno
      @id bigint
  as
      SELECT   cierre_turnos.id as cierre_turnos_id
              ,cierre_turnos.fecha as cierre_turnos_fecha
              ,cierre_turnos.turno_cuadrado as cierre_turnos_turno_cuadrado
              ,cierre_turnos.observacion as cierre_turnos_observacion
              ,cierre_turnos.diferencia_productos as cierre_turnos_diferencia_productos
              ,cierre_turnos.diferencia_pesos as cierre_turnos_diferencia_pesos
              ,cierre_turnos.fecha_negocio  as cierre_turnos_fecha_negocio
              ,Usuarios.User_intUserNo  as supervisor_User_intUserNo
              ,Usuarios.User_strFirstName as supervisor_User_strFirstName
              ,Usuarios.User_strLastName as supervisor_User_strLastName
              ,Usuarios.User_strEnabled as supervisor_User_strEnabled
              ,Usuarios.User_strHOPayrollCode as supervisor_User_strHOPayrollCode
              ,Usuarios.User_intPIN as supervisor_User_intPIN
              ,Usuarios.UserG_strCode as supervisor_UserG_strCode
              ,cierre_turnos_detalles.id as cierre_turnos_detalles_id
              ,cierre_turnos_detalles.cantidad_real as cierre_turnos_detalles_cantidad_real
              ,cierre_turnos_detalles.cantidad_entregada as cierre_turnos_detalles_cantidad_entregado
              ,cierre_turnos_detalles.cantidad_vendida as cierre_turnos_detalles_cantidad_vendida
              ,cierre_turnos_detalles.precio_unitario as cierre_turnos_detalles_precio_unitario
              ,Productos.*
      FROM  cierre_turnos, cierre_turnos_detalles,Usuarios ,  Productos
      WHERE cierre_turnos.id_turno = @id
          and cierre_turnos.id = cierre_turnos_detalles.id_cierre
          and  cierre_turnos.id_supervisor = Usuarios.User_intUserNo
          and cierre_turnos_detalles.id_producto = Productos.Item_strItemId
      order by cierre_turnos_detalles.id  
  GO




  CREATE PROCEDURE SP_PuntosVentas_sin_turno_activo_L
  AS
    SELECT * 
    FROM PuntosVentas
    WHERE Workstation_strCode not in 
                (select id_punto_venta from turnos where id_estado=1)
    Order by Workstation_strName
  GO

  INSERT INTO [dbo].[turnos_estados] ([nombre]) VALUES    ('Abierto')
  INSERT INTO [dbo].[turnos_estados] ([nombre]) VALUES    ('Cerrado')
  GO

  CREATE PROCEDURE SP_obtiene_ventas_de_vista_L
      @id_cajero bigint
  AS
      SELECT Session_POS.POSS_intPOSSessionNumber as IdSessionVISTA,Session_POS.POSS_dtmBusinessDate as FechaNegocioVISTA,Productos.Item_strItemId,Item_strItemShortDesc,COUNT(*) as cantidad, SUM(TransI_decNoOfItems) as cantidadVendida
      FROM Inventory,Session_POS,Productos
      where Inventory.TransI_lngPOSSessionID in  
            (select top 80  POSS_intPOSSessionNumber  from Session_POS  where User_intUserNo=@id_cajero order by POSS_intPOSSessionNumber desc)
          and Inventory.TransI_lngPOSSessionID=Session_POS.POSS_intPOSSessionNumber
          and Inventory.Item_strItemId = Productos.Item_strItemId
          and Session_POS.POSS_dtmBusinessDate  = (select max (session_temp.POSS_dtmBusinessDate ) from Session_POS as session_temp  where User_intUserNo=@id_cajero )
      group by Productos.Item_strItemId,Item_strItemShortDesc,Session_POS.POSS_intPOSSessionNumber,Session_POS.POSS_dtmBusinessDate  
      ORDER BY FechaNegocioVISTA
GO

create procedure sp_cantidad_ultimo_cierre_por_punto_venta
    @id_punto varchar(20)
  as  
  select id_producto,cantidad_real from cierre_turnos_detalles
  where id_cierre in (select top 1 id from cierre_turnos where id_punto_venta=@id_punto order by id desc)
GO


CREATE procedure sp_top_10_mas_vendido
as
  select top 10 id_producto as Item_strItemId,Productos.Item_strItemDescription as Item_strItemDescription,
  Productos.Item_curRetailPrice as Item_curRetailPrice ,Productos.Item_strItemShortDesc as Item_strItemShortDesc,
  Item_strStatus,SUM(cantidad_vendida) as cantidad_vendida
  from cierre_turnos_detalles ,Productos
  where Productos.Item_strItemId = cierre_turnos_detalles.id_producto
    and cierre_turnos_detalles.create_date >DATEADD(DAY ,-7 , GETDATE())
  group by id_producto,Productos.Item_strItemDescription,Item_strItemShortDesc,Productos.Item_curRetailPrice,Productos.Item_strItemShortDesc,Item_strStatus
  having SUM(cantidad_vendida)>0
  order by SUM(cantidad_vendida) desc
GO

  /*CREATE PROCEDURE sp_usuarios_cajeros_con_turno_activo_l
  as
    SELECT * 
    FROM Usuarios 
    WHERE  UserG_strCode='EMP' and User_strEnabled='Y' and User_strHOPayrollCode <> '' 
    and User_intUserNo in (select id_cajero from turnos where id_estado=1)
  GO*/

  /*SETEA LA BASE DE DATOS EN MODO MULTIUSUARIO */ 
  ALTER DATABASE SIC SET MULTI_USER
  GO

  use SIC
  GO


  /*
  SELECT   Sucursal.Cinema_strCode as Sucursal_codigo,  Sucursal.Cinema_strShortName as Sucursal
      ,cajero.User_strFirstName as Cajero_nombre, cajero.User_strLastName as Cajero_apellido, cajero.User_strHOPayrollCode
      ,supervisor_cierre.User_strFirstName as supervisor_cierre_nombre, supervisor_cierre.User_strLastName as supervisor_cierre_apellido
      ,turnos.fecha_inicio as hora_inicio_turno, fecha_termino as hora_cierre_turno, turnos.id_punto_venta as punto_venta
      ,cierre.fecha_negocio as fecha_cierre_cine, cierre.turno_cuadrado turno_cuadrado, cierre.diferencia_productos as diferencia_productos, cierre.diferencia_pesos 
  FROM turnos 
  Inner join Sucursal on turnos.cod_sucursal = Sucursal.Cinema_strBranchNo
  inner join Usuarios as cajero  on turnos.id_cajero = cajero.User_intUserNo 
  left join cierre_turnos as cierre on cierre.id_turno = turnos.id
  left join Usuarios as supervisor_apertura on turnos.id_supervisor_apertura = supervisor_apertura.User_intUserNo 
  left join Usuarios as supervisor_cierre on turnos.id_supervisor_cierre= supervisor_cierre.User_intUserNo 
  where turnos.id_estado=2
  */

  /*
  SELECT Inventory.TransI_decNoOfItems,Inventory.*
  FROM Inventory,Session_POS,Productos
  where Inventory.TransI_lngPOSSessionID in  
        (select top 1 POSS_intPOSSessionNumber  from Session_POS  where User_intUserNo=77 order by POSS_intPOSSessionNumber desc)
      and Inventory.TransI_lngPOSSessionID=Session_POS.POSS_intPOSSessionNumber
      and Inventory.Item_strItemId = Productos.Item_strItemId
  */


  /*
  CONSULTA PARA VER LOS ADMINISTRADORES Y SUPERVISORES
  select * from Usuarios where UserG_strCode in ('MAN','SUP')  and User_strEnabled ='Y' 
  */


  /*
  USUARIOS SIN SESSIONES EN POS
select * from Usuarios where UserG_strCode in ('EMP')  and User_strEnabled ='Y'  and User_intUserNo not in (select User_intUserNo from Session_POS)*/


